import xlrd
renshu = xlrd.open_workbook(filename=r"D:\德泰\baidu-员工的人员信息.xls")
sheet=renshu.sheet_by_index(0)
print("总共有",sheet.nrows-1,"个人")
yidong_count=0
liantong_count=0
dianxin_count=0
yidong={134,135,136,137,138,139,147,150,151,152,157,158,159,178,182,183,184,187,188,1703,1705,1706}
liantong={130,131,132,145,155,156,175,176,185,186,1704,1707,1708,1709,171}
dianxin={133,149,153,173,177,180,181,189,1700,1701,1702}
renshu = xlrd.open_workbook(filename=r"D:\德泰\baidu-员工的人员信息.xls")
sheet=renshu.sheet_by_index(0)
for row in range(1, sheet.nrows):
    phone_str = str(sheet.cell_value(row, 5)).strip()
    p1=int(phone_str[:3])
    p2=int(phone_str[:4])
    if p1 in yidong or p2 in yidong:
        yidong_count+=1
    elif p1 in liantong or p2 in liantong:
        liantong_count+=1
    elif p1 in dianxin or p2 in dianxin:
        dianxin_count+=1
total=yidong_count+liantong_count+dianxin_count
yidong_ratio=yidong_count/total*100
liantong_ratio=liantong_count/total*100
dianxin_ratio=dianxin_count/total*100
print(f"移动用户数量: {yidong_count}",f"占比: {yidong_ratio:.2f}%")
print(f"联通用户数量: {liantong_count}",f"占比: {liantong_ratio:.2f}%")
print(f"电信用户数量: {dianxin_count}",f"占比: {dianxin_ratio:.2f}%")
age_45 = 0
for i in range(1, sheet.nrows):
    age_value = sheet.cell_value(i, 7)
    if age_value > 45:
        age_45 += 1
print("超过45岁的人数:",age_45)
sal=0
for j in range(1,sheet.nrows):
    sal_value = sheet.cell_value(j,11)
    if sal_value > 3000 and sal_value>8000:
        sal += 1
print("超过3000的人数:",sal)
print("小于8000的人数:",sal)
count=0
for c in range(1,sheet.nrows):
    renyuan_count=sheet.cell_value(c,13)
    if "传媒" in renyuan_count :
        count+=1
print("传媒人数为:",count)
heilongjiang_count=0
beijing_count=0
fujian_count=0
sichaun_count=0
for k in range(1,sheet.nrows):
    renshu_count=sheet.cell_value(k,9)
    if "黑龙江" in renshu_count:
        heilongjiang_count+=1
    if "北京" in renshu_count:
        heilongjiang_count += 1
    if "福建" in renshu_count:
        heilongjiang_count += 1
    if "四川" in renshu_count:
        heilongjiang_count += 1
print("黑龙江人数为:",heilongjiang_count)
print("北京人数为:",beijing_count)
print("福建人数为:",fujian_count)
print("四川人数为:",sichaun_count)


